/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pa165.machineryrental.weblayer.utils;

import cz.muni.fi.pa165.machineryrental.weblayer.custommodels.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author jostri
 */
public class DBUtil {

	private DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public void initialize()  {
		System.out.println("DbUtil.initialize() *************************************** ");
		DataSource dataSource = getDataSource();

		Connection connection;
		Statement statement;
		
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
		}
		catch (SQLException ex) {
			return;
		}
		
		// Check, if exists tables
		try {
			
			ResultSet results = statement.executeQuery("SELECT u1.USER_ID AS ID, u1.USERNAME AS USERNAME, u1.ENABLED AS ENABLED, u2.ROLE AS ROLE, u2.USER_ROLE_ID AS ROLEID FROM user_authentication u1, user_authorization u2 WHERE u1.user_id = u2.user_id");
			
		} catch (SQLException ex) {
			// TABLES NOT EXISTS
			
			try {
				// Derby does not support DROP TABLE IF EXISTS, so this is solution.

				statement.executeUpdate("DROP TABLE USER_AUTHENTICATION");
			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				// Derby does not support DROP TABLE IF EXISTS, so this is solution.
				statement.executeUpdate("DROP TABLE USER_AUTHORIZATION");
			} catch (SQLException e) {
				e.printStackTrace();
			}

			try {
				statement.executeUpdate("CREATE TABLE USER_AUTHENTICATION (USER_ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY, USERNAME VARCHAR(50), PASSWORD VARCHAR(50), ENABLED BOOLEAN)");
				statement.executeUpdate("INSERT INTO USER_AUTHENTICATION (USERNAME, PASSWORD, ENABLED) VALUES('admin','pa165',TRUE)");

				statement.executeUpdate("CREATE TABLE USER_AUTHORIZATION (USER_ROLE_ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,USER_ID INTEGER, ROLE VARCHAR(50))");
				statement.executeUpdate("INSERT INTO USER_AUTHORIZATION (USER_ID, ROLE) VALUES(1,'ROLE_ADMIN')");


				statement.close();
				connection.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public List<User> getUsers() throws SQLException {


		DataSource dataSource = getDataSource();
		Connection connection = dataSource.getConnection();
		Statement statement = connection.createStatement();
		ResultSet results = statement.executeQuery("SELECT u1.USER_ID AS ID, u1.USERNAME AS USERNAME, u1.ENABLED AS ENABLED, u2.ROLE AS ROLE, u2.USER_ROLE_ID AS ROLEID FROM user_authentication u1, user_authorization u2 WHERE u1.user_id = u2.user_id");

		List<User> loaded = new ArrayList<User>();

		while (results.next()) {
			User user = new User();

			user.setId(results.getInt("ID"));
			user.setEnabled(results.getBoolean("ENABLED"));
			user.setUsername(results.getString("USERNAME"));
			user.setRole(results.getString("ROLE"));
			user.setRoleId(results.getInt("ROLEID"));

			loaded.add(user);
		}

		statement.close();
		connection.close();

		return loaded;
	}

	public void create(User user) throws SQLException {
		Connection connection = dataSource.getConnection();
		Statement statement = connection.createStatement();

		// Create user:
		PreparedStatement preparedStatement =
				connection.prepareStatement(
				"INSERT INTO USER_AUTHENTICATION (USERNAME, PASSWORD, ENABLED) VALUES(?,?,?)");

		preparedStatement.setString(1, user.getUsername());
		preparedStatement.setString(2, user.getPassword());
		preparedStatement.setBoolean(3, user.isEnabled());

		preparedStatement.executeUpdate();
		preparedStatement.close();

		// Get user ID:
		PreparedStatement ps = connection.prepareStatement("SELECT u1.USER_ID FROM user_authentication u1 WHERE u1.username = ?");
		ps.setString(1, user.getUsername());


		ResultSet results = ps.executeQuery();

		int id = 0;
		if (results.next()) {
			id = results.getInt("USER_ID");
		} else {
			statement.close();
			connection.close();
			ps.close();
			return;
		}
		ps.close();

		preparedStatement =
				connection.prepareStatement(
				"INSERT INTO USER_AUTHORIZATION (USER_ID, ROLE) VALUES(?,?)");
		preparedStatement.setInt(1, id);
		preparedStatement.setString(2, user.getRole());
		preparedStatement.executeUpdate();

		preparedStatement.close();
		statement.close();
		connection.close();
	}

	public void update(User user) throws SQLException {
		Connection connection = dataSource.getConnection();
		Statement statement = connection.createStatement();

		// Create user:
		PreparedStatement preparedStatement =
				connection.prepareStatement(
				"UPDATE USER_AUTHENTICATION SET PASSWORD = ?, ENABLED = ? WHERE USER_ID = ?");

		preparedStatement.setString(1, user.getPassword());
		preparedStatement.setBoolean(2, user.isEnabled());
		preparedStatement.setInt(3, user.getId());

		preparedStatement.executeUpdate();
		preparedStatement.close();


		preparedStatement =
				connection.prepareStatement(
				"UPDATE USER_AUTHORIZATION SET ROLE = ? WHERE USER_ROLE_ID = ?");
		preparedStatement.setString(1, user.getRole());
		preparedStatement.setInt(2, user.getRoleId());
		preparedStatement.executeUpdate();

		preparedStatement.close();
		statement.close();
		connection.close();

	}

	public void remove(int userID) throws SQLException {
		Connection connection = dataSource.getConnection();
		Statement statement = connection.createStatement();

		// Create user:
		PreparedStatement preparedStatement =
				connection.prepareStatement(
				"DELETE FROM USER_AUTHORIZATION WHERE USER_ID = ?");
		preparedStatement.setInt(1, userID);
		preparedStatement.executeUpdate();

		preparedStatement.close();

		preparedStatement = connection.prepareStatement(
				"DELETE FROM USER_AUTHENTICATION WHERE USER_ID = ?");
		preparedStatement.setInt(1, userID);
		preparedStatement.executeUpdate();

		preparedStatement.close();
		statement.close();
		connection.close();
	}

	public boolean existsUsername(String username) throws SQLException {
		Connection connection = dataSource.getConnection();
		Statement statement = connection.createStatement();

		PreparedStatement ps = connection.prepareStatement("SELECT u1.USER_ID FROM user_authentication u1 WHERE u1.username = ?");
		ps.setString(1, username);


		ResultSet results = ps.executeQuery();

		boolean hasUsername = results.next();

		ps.close();
		statement.close();
		connection.close();

		return hasUsername;
	}

	public User findUserById(int userID) throws SQLException {


		DataSource dataSource = getDataSource();
		Connection connection = dataSource.getConnection();
		Statement statement = connection.createStatement();

		PreparedStatement ps = connection.prepareStatement(
				"SELECT u1.USER_ID AS ID, u1.USERNAME AS USERNAME, u1.ENABLED AS ENABLED, u2.ROLE AS ROLE, u2.USER_ROLE_ID AS ROLEID FROM user_authentication u1, user_authorization u2 WHERE u1.user_id = u2.user_id AND u1.USER_ID = ?");
		ps.setInt(1, userID);


		ResultSet results = ps.executeQuery();

		List<User> loaded = new ArrayList<User>();

		if (results.next()) {
			User user = new User();

			user.setId(results.getInt("ID"));
			user.setEnabled(results.getBoolean("ENABLED"));
			user.setUsername(results.getString("USERNAME"));
			user.setRole(results.getString("ROLE"));
			user.setRoleId(results.getInt("ROLEID"));

			ps.close();
			statement.close();
			connection.close();
			return user;
		}

		ps.close();
		statement.close();
		connection.close();

		return null;
	}
}